﻿using CBS.Common;
using CBS.DB.CBSEntity;
using CBS.DB;
using CBS.Model.ManageModel;
using CBS.Model.ViewModel;
using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CBS.APP
{
    public class StudentBonusApp
    {
        public static bool CreateStudentBonus(StudentBonusViewModel bonus)
        {
            using CBSContext cbs = new();
            cbs.cbs_student_bonus.Add(new cbs_student_bonus
            {
                StudentId = bonus.StudentId,
                IntroduceStudentId = bonus.IntroduceStudentId,
                SemesterId = bonus.SemesterId,
            });

            return cbs.SaveChanges() > 0;
        }

        public static List<StudentBonusViewModel> GetList(Pagination page, string? studentName, string? introduceStudentName, int semesterId)
        {
            studentName ??= "";
            introduceStudentName ??= "";

            List<StudentBonusViewModel> list;
            string sql = $"select b.StudentId,b.StudentName,c.StudentId IntroduceStudentId,c.StudentName IntroduceStudentName,d.SemesterId,d.SemesterName, 0 IsFinished\r\nfrom cbs_student_bonus a\r\n\t\tleft join cbs_student b on a.StudentId = b.StudentId\r\n\t\tleft join cbs_student c on a.IntroduceStudentId = b.StudentId\r\n\t\tleft join cbs_semester d on a.SemesterId = d.SemesterId\r\nwhere (@semesterId = 0 or d.SemesterId = @semesterId)\r\nand ((@studentName = '' or b.StudentName like '@studentName') or (@introduceStudentName = '' or c.StudentName like '@introduceStudentName'))";
            MySqlParameter[] para =
            {
                new MySqlParameter("@studentName",$"%{studentName}%"),
                new MySqlParameter("@introduceStudentName",$"%{introduceStudentName}%"),
                new MySqlParameter("@semesterId",semesterId),
            };

            list = DBHelper.FindListOrderBy<StudentBonusViewModel>(sql, page, FromPositionInSql.First, para);

            return list;
        }

        public static StudentBonusViewModel GetForm(int SemesterId, int StudentId, int IntroduceStudentId)
        {
            using CBSContext cbs = new();
            var semester = cbs.cbs_semester.FirstOrDefault(t => t.SemesterId == SemesterId);
            var student = cbs.cbs_student.FirstOrDefault(t => t.StudentId == StudentId);
            var introduceStudent = cbs.cbs_student.FirstOrDefault(t=>t.StudentId == IntroduceStudentId);

            return new StudentBonusViewModel 
            {
                SemesterId = SemesterId,
                SemesterName = semester.SemesterName,
                StudentId = StudentId,
                StudentName = student.StudentName,
                IntroduceStudentId = IntroduceStudentId,
                IntroduceStudentName = introduceStudent.StudentName,
                IsSettled = false
            };
        }

        public static StudentBonusViewModel GetForm(int StudentId)
        {
            using CBSContext cbs = new();
            
            var bonus = cbs.cbs_student_bonus.FirstOrDefault(t => t.StudentId == StudentId);
            var student = cbs.cbs_student.FirstOrDefault(t => t.StudentId == StudentId);
            var introduceStudent = cbs.cbs_student.FirstOrDefault(t => t.StudentId == bonus.IntroduceStudentId);
            var semester = cbs.cbs_semester.FirstOrDefault(t => t.SemesterId == bonus.SemesterId);
            return new StudentBonusViewModel
            {
                SemesterId = semester.SemesterId,
                SemesterName = semester.SemesterName,
                StudentId = StudentId,
                StudentName = student.StudentName,
                IntroduceStudentId = introduceStudent.StudentId,
                IntroduceStudentName = introduceStudent.StudentName,
                IsSettled = false
            };
        }

        public static bool BatchSettledBonus(out string msg) 
        {
            msg = "";
            using CBSContext cbs = new();
            using var transaction = cbs.Database.BeginTransaction();
            try
            {
                //var data = (from bon in cbs.cbs_student_bonus
                //            join stu in cbs.cbs_student on bon);

                return true;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                transaction.Rollback();
                return false;
            }
        }
    }
}
